﻿SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

create view [dbo].[v_UDS_Flow_Staff_In_Step]
as
select
    o.staff_id,
    a.flow_id,
    a.step_id
from 
    uds_flow_member_bind a,
    uds_staff_in_role o,
    uds_staff p
where
    a.obj_id = o.role_id
    and a.obj_type = 3 /* role */
    and o.staff_id = p.staff_id
    and p.dimission = 0

union

select
    o.staff_id,
    a.flow_id,
    a.step_id 
from
    uds_flow_member_bind a,
    uds_staff_in_position o,
    uds_staff p
where
    a.obj_id = o.position_id
    and a.obj_type = 1 /* position */
    and o.staff_id = p.staff_id
    and p.dimission = 0

union

select
    o.staff_id,
    a.flow_id,
    a.step_id
from
    uds_flow_member_bind a,
    uds_staff_in_team o,
    uds_staff p
where
    a.obj_id = o.team_id
    and a.obj_type = 2
    and o.staff_id = p.staff_id
    and p.dimission = 0
    and o.member_type = 1

union

select
    o.staff_id,
    a.flow_id,
    a.step_id
from
    uds_flow_member_bind a,
    uds_staff_in_team o,
    uds_staff p
where
    a.obj_id = o.team_id
    and a.obj_type = 5 /* team leader */
    and o.staff_id = p.staff_id
    and p.dimission = 0
    and o.member_type = 2 /* team */

union

select
    o.staff_id,
    a.flow_id,
    a.step_id 
from
    uds_flow_member_bind a,
    uds_staff o
where
    a.obj_id = o.staff_id
    and a.obj_type = 4 /* staff */
    and o.dimission = 0
GO
